package main.staff;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

import main.util.JdbcUtil;
import main.util.SqlConstants;
import main.util.StringUtil;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;


public class CreateSqlGX {

	public static FileWriter staffInfo,role,serviceType,password,orm;
	static FileWriter del;
	
	public static Map<String,Integer> address;
	
	public static JdbcUtil db=new JdbcUtil();
	
	public static void main(String[] args) {
		InputStream inp;
		try {
			inp = new FileInputStream("D:/workbook36.xlsx");
			//InputStream inp = new FileInputStream("workbook.xlsx");
			
			Workbook wb = WorkbookFactory.create(inp);
			Sheet sheet = wb.getSheetAt(0);
			
			address=SqlConstants.getStaffAboutCol(sheet.getRow(0), 0, 13);

			 staffInfo = new FileWriter("D:/人员信息t_ucp_staffbasicinfo.sql");
			 role= new FileWriter("D:/角色t_ucp_staffrole.sql");
			 serviceType=new FileWriter("D:/业务类型t_ucp_staffserviceType.sql");
			 password=new FileWriter("D:/密码t_ucp_password.sql");
			 orm=new FileWriter("D:/映射t_ucp_staff_orm_kf.sql");
			 
			 del=new FileWriter("D:/删除.sql");
			
			 CreateTypesSql.topWrite(staffInfo);
			 CreateTypesSql.topWrite(role);
			 CreateTypesSql.topWrite(serviceType);
			 CreateTypesSql.topWrite(password);
			 CreateTypesSql.topWrite(del);
			 CreateTypesSql.topWrite(orm);
			 
			 operateSheet(sheet);
			 
			 SqlConstants.topWriteEnd(staffInfo);
			 SqlConstants.topWriteEnd(role);
			 SqlConstants.topWriteEnd(serviceType);
			 SqlConstants.topWriteEnd(password);
			 SqlConstants.topWriteEnd(orm);
			 SqlConstants.topWriteEnd(del);
			 
			staffInfo.close();
			role.close();
			serviceType.close();
			password.close();
			orm.close();
			del.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.close();
		}
	}
	
	public static void operateSheet(Sheet sheet){
		
		CreateTypesSql.address=address;
		
		try {
			for(int i=0;i<sheet.getLastRowNum()+1;i++){
				Row row = sheet.getRow(i);
				String staffid=CreateTypesSql.removeNull(row, address.get("staffId"));
				
				
				if(staffid.matches("[a-zA-Z]{2,3}[0-9]{4,5}")){
					String orgaId=null;
					if(address.get("orgaId")!=null){
						orgaId=CreateTypesSql.removeNull(row, address.get("orgaId"));
					}
					if(StringUtil.isEmpty(orgaId)){
						String oragName=CreateTypesSql.removeNull(row, address.get("orgaName")).trim();
						orgaId=getOrgaId(oragName,db);
					}
					
					if(orgaId==null){
						System.out.println(staffid+":loss  "+i);
						continue;
					}
					staffInfo.write(CreateTypesSql.staffbasicinfo(row,staffid,orgaId));
					//role.write(CreateTypesSql.staffrole(row,staffid));
					serviceType.write(CreateTypesSql.staffserviceType(row,staffid));
					password.write(CreateTypesSql.password(staffid));
					
					orm.write(CreateTypesSql.ormSql(row, staffid,"CRM",orgaId));
					orm.write(CreateTypesSql.ormSql(row, staffid,"BOSS",orgaId));
					
					//del.write("delete from t_ucp_staffservicetype where staffid='"+staffid+"';\n");
					//del.write("delete from t_ucp_staffbasicinfo where staffid='"+staffid+"';\n");
				//	del.write("delete from t_ucp_staffrole where staffid='"+staffid+"';\n");
					//del.write("delete from t_ucp_password where staffid='"+staffid+"';\n");
					
					System.out.println(staffid+":成功   "+i);
					
				}else{
					System.out.println(staffid+":loss  "+i);
				}
			}
		} catch (IOException e) {
			System.out.println("'''''''''''''''''''''''''"+sheet.getSheetName()+"添加失败");
			e.printStackTrace();
		}
	}
	
	public static String getOrgaId(String orgaName,JdbcUtil db){
		List<Map<String,Object>> orgaIds=db.queryForList("SELECT ORGAID from t_ucp_orgainfo WHERE ORGANAME='"+orgaName+"'");
		
		if(orgaIds.size()==1){
			return orgaIds.get(0).get("ORGAID").toString();
		}else if(orgaIds.size()>1){
			System.out.println("........."+orgaName+":组织机构名称重复"+orgaIds.toString());
			return null;
		}else{
			System.out.println("........."+orgaName+":组织机构名称不存在");
			return null;
		}
	}
	
	public static void main2(String[] args){
		System.out.println(CreateTypesSql.password("0682"));
	}
	
}
